package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Designer;
import com.ysd.util.DBManager;

public class DesignerDaoImpl { // 实现类,起名: 实体类名DaoImpl
	public List<Designer> getDesigner() {
		List<Designer> list = new ArrayList<Designer>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from designer";
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int designerid = rs.getInt("designerid");
				String designername = rs.getString("designername");
				String title = rs.getString("title");
				String headportrait = rs.getString("headportrait");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				Designer designer = new Designer(designerid, designername, title, headportrait, phone, email);
				list.add(designer);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	/**
	 * 根据设计师id删除设计师
	 * @param dId 要删除设计师的id
	 * @return 数据库受影响行数
	 */
	public int deleteDesignerById(int dId) {
		return DBManager.executeUpdate("delete from designer where designerid="+dId);
	}
	/***
	 *增加设计师
	 *返回受影响行数int类型
	 */
	public int addDesigner(Designer de) {
		return DBManager.executeUpdate("insert into designer VALUES (null,'"+de.getDesignername()+"','"+de.getTitle()+"','"+de.getHeadportrait()+"',"+de.getPhone()+",'"+de.getEmail()+"')");
	}
	public List<Designer> getDesignerByName(String name) {
		List<Designer> list = new ArrayList<Designer>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from designer where designername='"+name+"'";
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int designerid = rs.getInt("designerid");
				String designername = rs.getString("designername");
				String title = rs.getString("title");
				String headportrait = rs.getString("headportrait");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				Designer designer = new Designer(designerid, designername, title, headportrait, phone, email);
				list.add(designer);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/***
	 *根据id查询设计师
	 *返回设计师对象
	 */
	public Designer getDesignerByid(int id) {
		Designer designer=null;
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from designer where designerid="+id;
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int designerid = rs.getInt("designerid");
				String designername = rs.getString("designername");
				String title = rs.getString("title");
				String headportrait = rs.getString("headportrait");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				designer = new Designer(designerid, designername, title, headportrait, phone, email);
				
			}
			return designer;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 根据设计师id修改设计师
	 * @param dId 要修改设计师的id
	 * @return 数据库受影响行数
	 */
	public int updateDesignerById(Designer des) {
		return DBManager.executeUpdate("update designer set designername='"+des.getDesignername()+"',title='"+des.getTitle()+"',headportrait='"+des.getHeadportrait()+"',phone='"+des.getPhone()+"',email='"+des.getEmail()+"' where designerid="+des.getDesignerid());
	}
	
	/**
	 * 分页查询
	 * @param page
	 * @param size
	 * @return
	 */
	public List<Designer> getDesignerByPage(int page,int size) {
		List<Designer> dList = new ArrayList<Designer>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Designer d = null;
		try { // 正常
				// 2.创建Statement对象, 这个对象的作用是执行sql语句
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from designer limit "+(page-1)*size+", " + size;
			System.out.println(sql);
			// 4.执行sql语句
			rs = st.executeQuery(sql); // execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int designerid = rs.getInt("designerid");
				String designername = rs.getString("designername");
				String title = rs.getString("title");
				String headportrait = rs.getString("headportrait");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				d = new Designer(designerid, designername, title, headportrait, phone, email);
				dList.add(d);
			}
			return dList; // 正常执行返回用户集合
		} catch (SQLException e) { // 异常
			e.printStackTrace();
			return null; // 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs); // 关闭连接
		}
	}
}

